package model.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import util.DataSourceUtil;

import model.beans.ItemBean;
import model.dao.ItemDAO;

public class ItemDAOImp implements ItemDAO{

	@Override
	public List<ItemBean> getItemListByKeyword(String keyword) {
		List<ItemBean> result = new ArrayList<ItemBean>();
		try {
			DataSource dataSource = DataSourceUtil.getDataSource();
			Connection conn = dataSource.getConnection();
			Statement statement = conn.createStatement();			
			statement.executeUpdate("Set Schema roumani");
			ResultSet rs= statement.executeQuery("SELECT * FROM ITEM WHERE lower(NAME) like '%" + keyword.toLowerCase() +"%'");
			while (rs.next()){
				ItemBean item = new ItemBean();
				item.setNumber(rs.getString("NUMBER"));
				item.setName(rs.getString("NAME"));				
				item.setPrice(rs.getDouble("PRICE"));				
				item.setQuantity(rs.getInt("QTY"));
				item.setOnOrder(rs.getInt("ONORDER"));
				item.setReOrder(rs.getInt("REORDER"));
				item.setCatId(rs.getInt("CATID"));
				item.setSupId(rs.getInt("SUPID"));
				item.setCostPrice(rs.getDouble("COSTPRICE"));
				item.setUnit(rs.getString("UNIT"));
				result.add(item);
			}
			statement.close();
			conn.close();		
		} catch (Exception e){
			System.out.println("sql error!\n" + e);
		}
		return result;
	}

	@Override
	public List<ItemBean> getItemListByCatalogID(String catalogId) {
		List<ItemBean> result = new ArrayList<ItemBean>();
		try {
			DataSource dataSource = DataSourceUtil.getDataSource();
			Connection conn = dataSource.getConnection();
			Statement statement = conn.createStatement();			
			statement.executeUpdate("Set Schema roumani");
			ResultSet rs= statement.executeQuery("SELECT * FROM ITEM WHERE CATID =" + catalogId);
			while (rs.next()){
				ItemBean item = new ItemBean();
				item.setNumber(rs.getString("NUMBER"));
				item.setName(rs.getString("NAME"));				
				item.setPrice(rs.getDouble("PRICE"));				
				item.setQuantity(rs.getInt("QTY"));
				item.setOnOrder(rs.getInt("ONORDER"));
				item.setReOrder(rs.getInt("REORDER"));
				item.setCatId(rs.getInt("CATID"));
				item.setSupId(rs.getInt("SUPID"));
				item.setCostPrice(rs.getDouble("COSTPRICE"));
				item.setUnit(rs.getString("UNIT"));
				result.add(item); //TODO
			}
			statement.close();
			conn.close();		
		} catch (Exception e){
			System.out.println("sql error!\n" + e);
		}
		return result;
	}
	
	@Override
	public ItemBean getItemByNumber(String number) {
		ItemBean item = new ItemBean();
		try {
			DataSource dataSource = DataSourceUtil.getDataSource();
			Connection conn = dataSource.getConnection();
			Statement statement = conn.createStatement();			
			statement.executeUpdate("Set Schema roumani");
			ResultSet rs= statement.executeQuery("SELECT * FROM ITEM WHERE NUMBER =\'" + number+"\'");
			
			if (rs.next()){ 
				item.setNumber(rs.getString("number"));
				item.setName(rs.getString("name"));
				item.setPrice(rs.getDouble("price"));
				item.setQuantity(rs.getInt("qty"));
				item.setOnOrder(rs.getInt("onorder"));
				item.setReOrder(rs.getInt("reorder"));
				item.setCatId(rs.getInt("catid"));
				item.setSupId(rs.getInt("supid"));
				item.setCostPrice(rs.getDouble("costprice"));
				item.setUnit(rs.getString("unit"));
			}
			else{
				item = null;
			}
			statement.close();
			conn.close();		
		} catch (Exception e){
			System.out.println("sql error!\n" + e);
		}
		return item;
	}
}
